package com.mskj.p3.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.mskj.p3.dao.HobbyDao;
import com.mskj.p3.model.Hobby;
import com.mskj.p3.model.User;
import com.mskj.p3.util.JdbcUtil;

@Repository
public class HobbyDaoImp implements HobbyDao {
	@Resource
	private JdbcTemplate jt;
	
	@Override
	public List<Hobby> queryHobby(User u) {
		String sql = "select c.hid,hinfo from tbl_choose c,tbl_hobby h where c.hid = h.hid and u_id = ?";
		Object[] params = new Object[]{u.getUid()};
		List<Hobby> hobbylist = (List<Hobby>)jt.query(sql,params, new HobbyBeanMapper());
		return hobbylist;
	}
	
	private class HobbyBeanMapper implements RowMapper<Hobby>{

		@Override
		public Hobby mapRow(ResultSet rs, int pos) throws SQLException {
			Hobby h = new Hobby();
			h.setHid(rs.getString("hid"));
			h.setHinfo(rs.getString("hinfo"));
			return h;
		}
		
	}

	@Override
	public boolean deletehobby(Hobby h,User user) {
		String sql = "delete tbl_choose where u_id=? and hid=?";	
		Object[] params = new Object[] { user.getUid(), h.getHid() };
		int updateRecordNum = jt.update(sql, params);
		return updateRecordNum == 1;
		
	}
	
	
	@Override
  public boolean gethobby(Hobby h){
	  Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("select hid  hinfo from tbl_hobby where hinfo=? ");
			pst.setString(1,h.getHinfo());	
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.closeCon(con, pst);
		}
		return false; 
  }
	@Override
	public boolean addchoose(User u,Hobby h){
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("select hid  from tbl_hobby where hinfo=? ");
			pst.setString(1,h.getHinfo());	
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				String hid=rs.getString("hid");
				
				pst = con.prepareStatement("insert into tbl_choose values(?,?)");
				pst.setString(1, hid);
				pst.setString(2, u.getUid());
				if(pst.executeUpdate()==1){
				return true;
				}
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.closeCon(con, pst);
		}
		return false;
	}
	
	@Override
	public boolean addhobby( Hobby h) {
		Connection con = null;
		PreparedStatement pst = null;
		try {
			con = JdbcUtil.getCon();
			pst = con.prepareStatement("insert into tbl_hobby(hid,hinfo) values(seq_hobby.nextval,?)");
			pst.setString(1,h.getHinfo());
			return( pst.executeUpdate()==1);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.closeCon(con, pst);
		}
		return false;
	}

}
